﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;

    public class CustGridviewDA : ICustGridviewDA
    {
        private string COLUMN_INDEX = "@COLUMN_INDEX";
        private string CREATED_BY = "@CREATED_BY";
        private string CSS_CLASS = "@CSS_CLASS";
        private string FOOTER_TEXT = "@FOOTER_TEXT";
        private string FUNCTION_ID = "@FUNCTION_ID";
        private string GIRDVIEW_ID = "@GIRDVIEW_ID";
        private string HEADER_TEXT = "@HEADER_TEXT";
        private string PAGE_SIZE = "@PAGE_SIZE";
        private string SEQ_NO = "@SEQ_NO";
        private string SQL_DELETE_CUST_GRIDVIEW = " DELETE FROM SYSADM.CUST_GRIDVIEW WHERE USER_ID = @USER_ID AND FUNCTION_ID = @FUNCTION_ID AND GIRDVIEW_ID = @GIRDVIEW_ID AND SEQ_NO = @SEQ_NO ";
        private string SQL_DELETE_CUST_GRIDVIEW_BY_ID = " DELETE FROM SYSADM.CUST_GRIDVIEW WHERE USER_ID = @USER_ID AND FUNCTION_ID = @FUNCTION_ID AND GIRDVIEW_ID = @GIRDVIEW_ID ";
        private string SQL_INSERT_CUST_GRIDVIEW = " INSERT INTO SYSADM.CUST_GRIDVIEW ( USER_ID, FUNCTION_ID, GIRDVIEW_ID, SEQ_NO, COLUMN_INDEX, HEADER_TEXT, FOOTER_TEXT, CSS_CLASS, WIDTH, PAGE_SIZE, UDF1, UDF2, UDF3, UDF4, UDF5, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @USER_ID, @FUNCTION_ID, @GIRDVIEW_ID, @SEQ_NO, @COLUMN_INDEX, @HEADER_TEXT, @FOOTER_TEXT, @CSS_CLASS, @WIDTH, @PAGE_SIZE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_CUST_GRIDVIEW = " SELECT  CG.ROWID, CG.[USER_ID], CG.FUNCTION_ID, FL.MENU_STRING,  CG.GIRDVIEW_ID, CG.SEQ_NO, CG.COLUMN_INDEX, CG.HEADER_TEXT, CG.FOOTER_TEXT, CG.CSS_CLASS, CG.WIDTH, CG.PAGE_SIZE, CG.UDF1, CG.UDF2, CG.UDF3, CG.UDF4, CG.UDF5, CG.CREATED_BY, CG.CREATED_DATE, CG.UPDATED_BY, CG.UPDATED_DATE   FROM  SYSADM.CUST_GRIDVIEW CG LEFT JOIN SYSADM.FUNCTION_LIST FL ON CG.FUNCTION_ID = FL.FUNCTION_ID ";
        private string UDF1 = "@UDF1";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UPDATED_BY = "@UPDATED_BY";
        private string USER_ID = "@USER_ID";
        private string WIDTH = "@WIDTH";

        public int DeleteCustGridview(string functionID, string gridviewID, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID), new SqlParameter("@FUNCTION_ID", functionID), new SqlParameter("@GIRDVIEW_ID", gridviewID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CUST_GRIDVIEW_BY_ID.Replace("@_@", whLoginID), paras);
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteCustGridview(DataBase dataBase, DbTransaction tran, string functionID, string gridviewID, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID), new SqlParameter("@FUNCTION_ID", functionID), new SqlParameter("@GIRDVIEW_ID", gridviewID) };
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_CUST_GRIDVIEW_BY_ID.Replace("@_@", whLoginID), paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public List<CustGridviewInfo> GetCustGridviewInfos(string functionID, string gridviewID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CUST_GRIDVIEW.Replace("@_@", whLoginID) + " WHERE CG.USER_ID = @USER_ID AND CG.FUNCTION_ID = @FUNCTION_ID AND CG.GIRDVIEW_ID = @GIRDVIEW_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID), new SqlParameter("@FUNCTION_ID", functionID), new SqlParameter("@GIRDVIEW_ID", gridviewID) };
            List<CustGridviewInfo> CustGridviewInfos = new List<CustGridviewInfo>();
            CustGridviewInfo custGridviewInfo = null;
            IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                custGridviewInfo = new CustGridviewInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["USER_ID"].ToString(), reader["FUNCTION_ID"].ToString(), reader["MENU_STRING"].ToString(), reader["GIRDVIEW_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, (reader["COLUMN_INDEX"].ToString() != "") ? int.Parse(reader["COLUMN_INDEX"].ToString()) : 0, reader["HEADER_TEXT"].ToString(), reader["FOOTER_TEXT"].ToString(), reader["CSS_CLASS"].ToString(), (reader["WIDTH"].ToString() != "") ? int.Parse(reader["WIDTH"].ToString()) : 0, (reader["PAGE_SIZE"].ToString() != "") ? int.Parse(reader["PAGE_SIZE"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                CustGridviewInfos.Add(custGridviewInfo);
            }
            return CustGridviewInfos;
        }

        public List<CustGridviewInfo> GetCustGridviewInfos(DataBase dataBase, DbTransaction tran, string functionID, string gridviewID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_CUST_GRIDVIEW.Replace("@_@", whLoginID) + " WHERE CG.USER_ID = @USER_ID AND CG.FUNCTION_ID = @FUNCTION_ID AND CG.GIRDVIEW_ID = @GIRDVIEW_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@USER_ID", pubParasInfo.UserID), new SqlParameter("@FUNCTION_ID", functionID), new SqlParameter("@GIRDVIEW_ID", gridviewID) };
            List<CustGridviewInfo> CustGridviewInfos = new List<CustGridviewInfo>();
            CustGridviewInfo custGridviewInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                custGridviewInfo = new CustGridviewInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["USER_ID"].ToString(), reader["FUNCTION_ID"].ToString(), reader["MENU_STRING"].ToString(), reader["GIRDVIEW_ID"].ToString(), (reader["SEQ_NO"].ToString() != "") ? int.Parse(reader["SEQ_NO"].ToString()) : 0, (reader["COLUMN_INDEX"].ToString() != "") ? int.Parse(reader["COLUMN_INDEX"].ToString()) : 0, reader["HEADER_TEXT"].ToString(), reader["FOOTER_TEXT"].ToString(), reader["CSS_CLASS"].ToString(), (reader["WIDTH"].ToString() != "") ? int.Parse(reader["WIDTH"].ToString()) : 0, (reader["PAGE_SIZE"].ToString() != "") ? int.Parse(reader["PAGE_SIZE"].ToString()) : 0, reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                CustGridviewInfos.Add(custGridviewInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return CustGridviewInfos;
        }

        public int InsertCustGridview(List<CustGridviewInfo> custGridviewInfos, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int i = 0; i < custGridviewInfos.Count; i++)
                {
                    SqlParameter[] paras = this.Set_CustGridview_Parameters(custGridviewInfos[i]);
                    if (paras != null)
                    {
                        result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CUST_GRIDVIEW.Replace("@_@", whLoginID), paras);
                    }
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertCustGridview(DataBase dataBase, DbTransaction tran, List<CustGridviewInfo> custGridviewInfos, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < custGridviewInfos.Count; i++)
                {
                    SqlParameter[] paras = this.Set_CustGridview_Parameters(custGridviewInfos[i]);
                    if (paras != null)
                    {
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_CUST_GRIDVIEW.Replace("@_@", whLoginID), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_CustGridview_Parameters(CustGridviewInfo custGridviewInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.USER_ID, SqlDbType.VarChar, 30), new SqlParameter(this.FUNCTION_ID, SqlDbType.VarChar, 30), new SqlParameter(this.GIRDVIEW_ID, SqlDbType.VarChar, 50), new SqlParameter(this.SEQ_NO, SqlDbType.Int, 4), new SqlParameter(this.COLUMN_INDEX, SqlDbType.Int, 4), new SqlParameter(this.HEADER_TEXT, SqlDbType.VarChar, 30), new SqlParameter(this.FOOTER_TEXT, SqlDbType.VarChar, 30), new SqlParameter(this.CSS_CLASS, SqlDbType.VarChar, 30), new SqlParameter(this.WIDTH, SqlDbType.Int, 4), new SqlParameter(this.PAGE_SIZE, SqlDbType.Int, 4), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 30)
             };
            paramArray[0].Value = custGridviewInfo.UserID;
            paramArray[1].Value = custGridviewInfo.FunctionID;
            paramArray[2].Value = custGridviewInfo.GirdviewID;
            paramArray[3].Value = custGridviewInfo.SeqNo;
            paramArray[4].Value = custGridviewInfo.ColumnIndex;
            if (!string.IsNullOrEmpty(custGridviewInfo.HeaderText))
            {
                paramArray[5].Value = custGridviewInfo.HeaderText;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(custGridviewInfo.FooterText))
            {
                paramArray[6].Value = custGridviewInfo.FooterText;
            }
            else
            {
                paramArray[6].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(custGridviewInfo.CssClass))
            {
                paramArray[7].Value = custGridviewInfo.CssClass;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            paramArray[8].Value = custGridviewInfo.Width;
            paramArray[9].Value = custGridviewInfo.PageSize;
            if (!string.IsNullOrEmpty(custGridviewInfo.Udf1))
            {
                paramArray[10].Value = custGridviewInfo.Udf1;
            }
            else
            {
                paramArray[10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(custGridviewInfo.Udf2))
            {
                paramArray[11].Value = custGridviewInfo.Udf2;
            }
            else
            {
                paramArray[11].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(custGridviewInfo.Udf3))
            {
                paramArray[12].Value = custGridviewInfo.Udf3;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(custGridviewInfo.Udf4))
            {
                paramArray[13].Value = custGridviewInfo.Udf4;
            }
            else
            {
                paramArray[13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(custGridviewInfo.Udf5))
            {
                paramArray[14].Value = custGridviewInfo.Udf5;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = custGridviewInfo.CreatedBy;
            paramArray[0x10].Value = custGridviewInfo.UpdatedBy;
            return paramArray;
        }
    }
}

